package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.cons.GroupType;
import cn.ztydata.teachingsystem.heaven.entity.Teacher;
import cn.ztydata.teachingsystem.heaven.entity.TeacherStatistics;
import cn.ztydata.teachingsystem.heaven.entity.User;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;


/**
 * 教师Dao
 *
 * @author fanruiping
 * @since 2014-11-27
 */
@Repository
public class TeacherDao extends BaseDao {
    /**
     * 教师列表
     * @param keyword 搜索关键字
     * @param deptNum 系部编码
     * @param pageNum 页码
     * @param pageSize 每页显示条数
     * @return Page
     *
     * @author fanruiping
     * @since 2014-12-11
     */
    public Page<Teacher> getList(String keyword, String deptNum, int pageNum, int pageSize){
        String where = "WHERE 1=1";

        if(!"".equals(keyword)){
            log.debug("如果keyword不为空：!空.equals(keyword)");
            where += " AND (UPPER(sNumber) LIKE :keyword OR sName LIKE :keyword)";
        }

        if(!"ALL".equals(deptNum)){
            log.debug("如果deptNum不等ALL：!ALL.equals(deptNum)");
            where += " AND sDeptNum=:deptNum";
        }

        String totalSql = "SELECT COUNT(id) FROM t_teacher " + where + " LIMIT 1";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("keyword", "%"+keyword.toUpperCase()+"%").addValue("deptNum",deptNum)
                .addValue("count", (pageNum - 1) * pageSize).addValue("pageSize", pageSize);

        int total = namedParameterJdbcTemplate.queryForObject(totalSql, params, Integer.class);

        if(total < 1){
            log.debug("如果total小于1：total < 1");
            return new Page<>();
        }

        String dataSql = "SELECT id,sNumber,sName,sPassword,tiSex,dtBirthday,sDeptNum,sDegree,sTitle,sEducation,sRemark " +
                "FROM t_teacher "+where+" LIMIT :count,:pageSize";

        List<Teacher> entities = namedParameterJdbcTemplate.query(dataSql, params, CreateListMapper());

        log.debug("教师列表");
        return new Page<>(total,pageSize,pageNum,entities);
    }

    /**
     * 验证系部下是否有教师
     * @param deptNum 系部编码
     * @return Boolean
     *
     * @author fanruiping
     * @since 2014-11-29
     */
    public Boolean checkByDeptNum(String deptNum){
        String sql = "SELECT COUNT(id) FROM t_teacher WHERE sDeptNum=:deptNum LIMIT 1";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("deptNum", deptNum);

        log.debug("根据系部编码获取教师条数");

        return namedParameterJdbcTemplate.queryForObject(sql, params, Integer.class) == 1;
    }

    /**
     * 更新教师数据
     * @param teacher 教师实体类
     * @return boolean
     *
     * @author fanruiping
     * @since 2014-11-27
     */
    public boolean modify(Teacher teacher){
        String sql = "UPDATE t_teacher SET sName=:name,dtBirthday=:birthday,tiSex=:sex,sDegree=:degree," +
                "sTitle=:title,sEducation=:education,sPicture=:picture,sRemark=:remark WHERE id=:id";

        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("id", teacher.getId());
        params.addValue("name", teacher.getName());
        params.addValue("birthday", teacher.getBirthday());
        params.addValue("sex", teacher.getSex());
        params.addValue("degree", teacher.getDegree());
        params.addValue("title", teacher.getTitle());
        params.addValue("education", teacher.getEducation());
        params.addValue("picture", teacher.getPicture());
        params.addValue("remark", teacher.getRemark());

        log.debug("update teacherInfo");

        return namedParameterJdbcTemplate.update(sql, params) == 1;
    }

    /**
     * 根据编号获取详细信息
     * @param id 编号
     * @return Teacher
     *
     * @author fanruiping
     * @since 2014-11-27
     */
    public Teacher getById(long id){
        try {
            String sql = "SELECT * FROM t_teacher WHERE id=:id";

            MapSqlParameterSource params = new MapSqlParameterSource();
            params.addValue("id", id);

            log.debug("根据编号获取详细信息");
            return namedParameterJdbcTemplate.queryForObject(sql, params, CreateMapper());
        }catch (EmptyResultDataAccessException ex){
            log.debug("根据编号获取详细信息失败");
            return null;
        }
    }

    /**
     * 验证用户登录信息
     * @param user 登录信息实体类
     * @return User
     *
     * @author fanruiping
     * @since 2014-12-03
     */
    public Teacher checkNumberAndPassword(User user){
        try {
            String sql = "SELECT * FROM t_teacher WHERE sNumber=:number AND sPassword=:password LIMIT 1";

            MapSqlParameterSource params = new MapSqlParameterSource().addValue("number", user.getNumber()).addValue("password", user.getPassword());

            log.debug("根据登录信息获取到用户信息:return Teacher");

            return namedParameterJdbcTemplate.queryForObject(sql, params, CreateMapper());
        }catch (EmptyResultDataAccessException ex){
            log.debug("根据登录信息获取用户信息发生Empty异常:EmptyResultDataAccessException");

            return null;
        }
    }

    /**
     * 批量新增教师
     * @param teacherList 教师信息集合
     * @return int[]
     *
     * @author cx
     * @since 2014-11-29
     */
    public int[] saveTeachers(final List<Teacher> teacherList) {
        String sql = "INSERT INTO t_teacher(sNumber,sName,sPinyin,sDeptNum,dtBirthday,tiSex,sDegree,sTitle,sEducation) " +
                "VALUES(:number,:name,:pinyin,:deptNum,:birthday,:sex,:degree,:title,:education)";

        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(teacherList.toArray());
        log.debug("return 批量导入教师数据的结果");
        return namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

    /**
     * ResultSet转换为Teacher实体（全部数据）
     * 获取需要统计的教师基本信息
     * @param deptNum 系部编码
     * @param keyword 搜索关键字
     * @return List
     *
     * @author cx
     * @since 2014-12-03
     */
    public List<TeacherStatistics> getListForCount(final String deptNum, final String keyword) {
        StringBuilder sql = new StringBuilder("SELECT id,sNumber,sName FROM t_teacher");

        StringBuilder whereSql = new StringBuilder(" WHERE sDeptNum=:deptNum");
        //定义绑定参数
        MapSqlParameterSource sps = new MapSqlParameterSource().addValue("deptNum", deptNum);

        //拼接搜索关键字
        if (!StringUtils.isEmpty(keyword)) {
            log.debug("keyword is not empty:!StringUtils.isEmpty(keyword)");
            whereSql.append(" AND (UPPER(sNumber) LIKE :keyword OR UPPER(sName) LIKE :keyword)");
            sps.addValue("keyword", "%" + keyword.toUpperCase() + "%");
        }

        log.debug("获取需要统计的教师基本信息");
        return namedParameterJdbcTemplate.query(sql.append(whereSql).toString(), sps, new RowMapper<TeacherStatistics>() {
            @Override
            public TeacherStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                TeacherStatistics entity = new TeacherStatistics();

                entity.setId(resultSet.getLong("id"));
                entity.setNumber(resultSet.getString("sNumber"));
                entity.setName(resultSet.getString("sName"));

                return entity;
            }
        });
    }

    /**
     * 获取与数据库中重复的工号
     * @param teacherNumSet 教师工号集合
     * @return String
     *
     * @author cx
     * @since 2014-12-04
     */
    public String getDuplicateNum(final Set<String> teacherNumSet) {
        if (teacherNumSet.isEmpty()) {
            log.debug("导入的教师工号集合为空");

            log.info("教师工号集合为0");
            return null;
        }

        try {
            String sql = "SELECT sNumber FROM t_teacher WHERE sNumber IN (:numSet) LIMIT 1";
            SqlParameterSource sps = new MapSqlParameterSource().addValue("numSet", teacherNumSet);

            log.debug("return 表中与导入数据重复的教师工号");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, String.class);
        } catch (EmptyResultDataAccessException ex) {
            log.debug("数据库中无重复的教师工号");

            log.info("数据库中无重复的教师工号");
            return null;
        }
    }

    /**
     * 根据编号集合获取教师集合
     * @param ids 编号集合
     * @return List
     *
     * @author fanruiping
     * @since 2014-12-11
     */
    public List<Teacher> getByIds(List<Long> ids){
        String sql = "SELECT id,sNumber,sName,sDeptNum,tiSex FROM t_teacher WHERE id IN(:ids)";

        log.debug("根据编号集合获取教师集合");

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("ids",ids),CreateSimpleMapper());
    }

    /**
     * 查询教师团队信息
     * @param teacherIds 教师ID集合
     * @return List
     *
     * @author cx
     * @since 2014-12-13
     */
    public List<Teacher> getAll(Set<Long> teacherIds) {
        String sql = "SELECT t.id,t.sNumber,t.sName,d.sName AS deptName,t.tiSex,t.sDegree,t.sTitle,t.sEducation,t.sPicture,t.sRemark " +
                "FROM t_teacher t LEFT JOIN t_department d ON t.sDeptNum=d.sNumber WHERE t.id IN (:teacherIds)";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("teacherIds", teacherIds);

        log.debug("获取团队教师信息");
        return namedParameterJdbcTemplate.query(sql, sps, new RowMapper<Teacher>() {
            @Override
            public Teacher mapRow(ResultSet rs, int rowNum) throws SQLException {
                Teacher entity = new Teacher();

                entity.setId(rs.getLong("id"));
                entity.setNumber(rs.getString("sNumber"));
                entity.setName(rs.getString("sName"));
                entity.setDeptName(rs.getString("deptName"));
                entity.setSex(rs.getInt("tiSex"));
                entity.setDegree(rs.getString("sDegree"));
                entity.setTitle(rs.getString("sTitle"));
                entity.setEducation(rs.getString("sEducation"));
                entity.setPicture(rs.getString("sPicture"));
                entity.setRemark(rs.getString("sRemark"));

                return entity;
            }
        });
    }

    /**
     * 查询教师列表（教师团队）
     * @param pageNum 页码
     * @param pageSize 每页条数
     * @param deptNum 系部编码
     * @param keywords 搜索关键字
     * @return Page
     *
     * @author cx
     * @since 2014-12-15
     */
    public Page<Teacher> getAllForTeam(final int pageNum, final int pageSize, final String deptNum, String[] keywords) {
        String sql = "SELECT id,sNumber,sName,sDeptNum,tiSex FROM t_teacher";
        String countSql = "SELECT COUNT(*) FROM t_teacher";

        MapSqlParameterSource sps = new MapSqlParameterSource();

        //构建where条件
        StringBuilder where = new StringBuilder(" WHERE 1=1");
        //判断系部编码
        if (!StringUtils.isEmpty(deptNum)) {
            log.debug("系部编码 is empty:!StringUtils.isEmpty(deptNum)");
            where.append(" AND sDeptNum=:deptNum");
            sps.addValue("deptNum", deptNum);
        }

        //判断搜索关键字
        if (keywords.length > 0) {
            log.debug("搜索关键字集合非空:keywords.length > 0");
            where.append(" AND (");
            log.debug("循环搜索关键字集合");
            for (int i = 0; i < keywords.length; i++) {
                where.append("UPPER(sName) LIKE :pattern").append(i).append(" OR ");
                sps.addValue("pattern" + i, "%" + keywords[i].toUpperCase() + "%");
            }

            //去除多余字符
            where.delete(where.length() - 4, where.length());
            where.append(")");
        }

        sql = sql + where.toString();
        countSql = countSql + where.toString();

        //获取数据总量
        int total = namedParameterJdbcTemplate.queryForObject(countSql, sps, Integer.class);
        if (total == 0) {
            log.debug("查询教师数量总数为0:total == 0");
            return new Page<>();
        }

        //追加limit限制
        sql += getLimitStr(pageNum, pageSize);

        List<Teacher> teachers = namedParameterJdbcTemplate.query(sql, sps, new RowMapper<Teacher>() {
            @Override
            public Teacher mapRow(ResultSet rs, int rowNum) throws SQLException {
                Teacher entity = new Teacher();

                entity.setId(rs.getLong("id"));
                entity.setNumber(rs.getString("sNumber"));
                entity.setName(rs.getString("sName"));
                entity.setDeptNum(rs.getString("sDeptNum"));
                entity.setSex(rs.getInt("tiSex"));

                return entity;
            }
        });

        log.debug("return 教师分页集合");
        return new Page<>(total, pageSize, pageNum, teachers);
    }

    /**
     * 查询教师姓名与头像
     * @param teacherIds 教师ID集合
     * @return List
     *
     * @author cx
     * @since 2014-12-15
     */
    public List<Teacher> getAllForPic(Set<Long> teacherIds) {
        String sql = "SELECT id,sName,sPicture FROM t_teacher WHERE id IN (:teacherIds)";

        log.debug("查询团队教师姓名与头像");

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("teacherIds", teacherIds), new RowMapper<Teacher>() {
            @Override
            public Teacher mapRow(ResultSet rs, int rowNum) throws SQLException {
                Teacher entity = new Teacher();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setPicture(rs.getString("sPicture"));

                return entity;
            }
        });
    }


    /**
     * 修改用户密码
     * @param password 密码
     * @param userId 用户编号
     * @return int
     *
     * @author fanruiping
     * @since 2014-12-29
     */
    public int updatePassword(String password,Long userId){
        String sql = "UPDATE t_teacher SET sPassword=MD5(:password) WHERE id=:userId";

        log.debug("修改用户密码");

        return namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource().addValue("password", password).addValue("userId", userId));
    }

    /**
     * 验证用户密码
     * @param teacherId 用户编号
     * @param password 密码
     * @return Boolean
     *
     * @author fanruiping
     * @since 2014-12-29
     */
    public Boolean checkUserPassword(Long teacherId,String password){
        String sql = "SELECT COUNT(*) FROM t_teacher WHERE id=:id AND sPassword=MD5(:password)";

        log.debug("验证用户密码");

        return namedParameterJdbcTemplate.queryForObject(sql,new MapSqlParameterSource().addValue("id",teacherId).addValue("password",password),Integer.class) == 1;
    }

    /**
     * 获取指定系部下的n个教师信息
     *
     * @author chenxin
     * @since 2015/1/11
     */
    public List<Teacher> getListForIndex(final String deptNum, final int limit) {
        String sql = "SELECT id,sName,sPicture FROM t_teacher WHERE sDeptNum=:deptNum LIMIT :limit";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("deptNum", deptNum)
                .addValue("limit", limit);

        log.debug("获取指定系部下的n个教师信息");
        return namedParameterJdbcTemplate.query(sql, sps, new RowMapper<Teacher>() {
            @Override
            public Teacher mapRow(ResultSet rs, int rowNum) throws SQLException {
                Teacher entity = new Teacher();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setPicture(rs.getString("sPicture"));

                return entity;
            }
        });
    }

    /**
     * 获取指定教师的名称
     * @param groupId 课程群组编号
     * @return String
     *
     * @author fanruiping
     * @since 2015-04-02
     */
    public String getNameByCourseGroup(Long groupId){
        try {
            String sql = "SELECT sName FROM t_teacher WHERE sNumber=(SELECT sTeacherNum FROM t_course_group WHERE id=:groupId)";

            return namedParameterJdbcTemplate.queryForObject(sql,new MapSqlParameterSource().addValue("groupId",groupId),String.class);
        }catch (EmptyResultDataAccessException ex){
            return "";
        }
    }

    /**
     * ResultSet转换为Teacher实体
     * @return RowMapper
     *
     * @author fanruiping
     * @since 2014-11-27
     */
    private RowMapper<Teacher> CreateMapper(){
        return new RowMapper<Teacher>() {
            @Override
            public Teacher mapRow(ResultSet rs, int i) throws SQLException {
                Teacher entity = new Teacher();

                entity.setId(rs.getLong("id"));
                entity.setNumber(rs.getString("sNumber"));
                entity.setName(rs.getString("sName"));
                entity.setPassword(rs.getString("sPassword"));
                entity.setDeptNum(rs.getString("sDeptNum"));
                entity.setBirthday(rs.getDate("dtBirthday"));
                entity.setSex(rs.getInt("tiSex"));
                entity.setDegree(rs.getString("sDegree"));
                entity.setTitle(rs.getString("sTitle"));
                entity.setEducation(rs.getString("sEducation"));
                entity.setPicture(rs.getString("sPicture"));
                entity.setRemark(rs.getString("sRemark"));

                return entity;
            }
        };
    }

    /**
     * ResultSet转换为Teacher实体
     * @return RowMapper
     *
     * @author fanruiping
     * @since 2014-11-27
     */
    private RowMapper<Teacher> CreateListMapper(){
        return new RowMapper<Teacher>() {
            @Override
            public Teacher mapRow(ResultSet rs, int i) throws SQLException {
                Teacher entity = new Teacher();

                entity.setId(rs.getLong("id"));
                entity.setNumber(rs.getString("sNumber"));
                entity.setName(rs.getString("sName"));
                entity.setDeptNum(rs.getString("sDeptNum"));
                entity.setSex(rs.getInt("tiSex"));
                entity.setBirthday(rs.getDate("dtBirthday"));
                entity.setDegree(rs.getString("sDegree"));
                entity.setEducation(rs.getString("sEducation"));
                entity.setTitle(rs.getString("sTitle"));
                entity.setRemark(rs.getString("sRemark"));

                return entity;
            }
        };
    }

    /**
     * ResultSet转换为Teacher实体（部分数据）
     * @return RowMapper
     *
     * @author fanruiping
     * @since 2014-11-27
     */
    private RowMapper<Teacher> CreateSimpleMapper(){
        return new RowMapper<Teacher>() {
            @Override
            public Teacher mapRow(ResultSet rs, int i) throws SQLException {
                Teacher entity = new Teacher();

                entity.setId(rs.getLong("id"));
                entity.setNumber(rs.getString("sNumber"));
                entity.setName(rs.getString("sName"));
                entity.setDeptNum(rs.getString("sDeptNum"));
                entity.setSex(rs.getInt("tiSex"));

                return entity;
            }
        };
    }

    /**
     * 根据工号差ID
     * @param num 工号
     * @return long
     *
     * @author wyh
     * @since 2015-03-26
     */
    public long getIdByNum(String num){
        String sql = "SELECT id FROM t_teacher WHERE sNumber=:num";

        try {
            log.debug("返回 教师id");
            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("num", num), Long.class);
        } catch (EmptyResultDataAccessException ex) {
            log.debug("查询教师ID为空 返回0");
            return 0;
        }
    }

    /**
     * 查询教师姓名
     * @param teacherNums 教师ID集合
     * @return List
     *
     * @author wyh
     * @since 2015-4-8
     */
    public Map<String,String> getNameByNums(List<String> teacherNums) {
        final Map<String,String> result = new HashMap<>();

        String sql = "SELECT id,sName,sNumber FROM t_teacher WHERE sNumber IN (:teacherNums)";

        log.debug("返回 教师id,姓名与工号");

        namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("teacherNums", teacherNums), new RowMapper<Object>() {
            @Override
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                result.put(rs.getString("sNumber"),rs.getString("sName"));

                return null;
            }
        });

        return result;
    }

    /**
     * 根据教师ID分页获取教师列表
     *
     * @param teacherIds 班级代码
     * @param letter   姓名首字母
     * @param pageNum  当前页码
     * @param pageSize 每页条数
     * @param search   关键字
     * @return Page<Teacher>
     * @author lyj
     * @since 2015-5-6
     */
    public Page<Teacher> getTeachersByIds(final Set<Long> teacherIds, final String letter, final int pageNum, final int pageSize, final String search) {
        StringBuilder sql = new StringBuilder("SELECT te.id,te.sNumber,te.sName AS teName,te.sPicture,de.sName AS deName FROM t_teacher te " +
                "JOIN t_department de ON te.sDeptNum = de.sNumber ");
        StringBuilder sqlCount = new StringBuilder("SELECT COUNT(te.Id) FROM t_teacher te " +
                "JOIN t_department de ON te.sDeptNum = de.sNumber ");
        StringBuilder where = new StringBuilder(" WHERE 1 = 1");

        MapSqlParameterSource param = new MapSqlParameterSource();

        //过滤课程代码
        if (teacherIds.size() != 0) {
            log.debug("过滤课程代码teacherIds.size() != 0");

            where.append(" AND te.id IN (:teacherIds)");
            param.addValue("teacherIds", teacherIds);
        }

        //过滤姓名首字母
        if (!letter.equals("0")) {
            log.debug("过滤姓名首字母!letter.equals(\"0\")");

            where.append(" AND UPPER(te.sPinyin) LIKE :letter");
            param.addValue("letter", letter.toUpperCase() + "%");
        }

        //过滤姓名
        if (!search.isEmpty()) {
            log.debug("过滤姓名!search.isEmpty()");

            where.append(" AND (te.sName LIKE :search OR te.sNumber LIKE :search)");
            param.addValue("search", "%" + search + "%");
        }

        //获取查询总条数
        int total = namedParameterJdbcTemplate.queryForObject(sqlCount.append(where).toString(), param, Integer.class);

        //获取总条数
        if (total == 0) {
            log.debug("获取总条数total == 0");
            return new Page<>();
        }

        //追加limit限制
        where.append(getLimitStr(pageNum, pageSize));

        List<Teacher> teacherList = namedParameterJdbcTemplate.query(sql.append(where).toString(), param, new RowMapper<Teacher>() {
            @Override
            public Teacher mapRow(ResultSet resultSet, int i) throws SQLException {
                Teacher teacher = new Teacher();

                teacher.setId(resultSet.getLong("id"));
                teacher.setNumber(resultSet.getString("sNumber"));
                teacher.setName(resultSet.getString("teName"));
                teacher.setDeptName(resultSet.getString("deName"));
                teacher.setPicture(resultSet.getString("sPicture"));

                return teacher;
            }
        });

        log.debug("根据教师ID分页获取教师列表");
        return new Page<>(total, pageSize, pageNum, teacherList);
    }



    /**
     * 专业群组活跃教师统计
     * @param groupId 群组ID <=> 专业ID
     * @param creatorId 创建者ID
     * @return int
     * @author lyj
     * @since 2015-5-6
     */
    public int getClassCountAll(final long groupId, final long creatorId) {
        StringBuilder sql = new StringBuilder("SELECT speakCount+resourceCount AS countAll FROM " +
                "(SELECT " +
                "(SELECT COUNT(s.id) FROM t_speak s WHERE s.iCreatorId = :creatorId AND s.tiRole = :role AND s.iGroupId = :groupId AND s.tiGroupType = :groupType) speakCount," +
                "(SELECT COUNT(d.id) FROM t_daily d WHERE d.iCreatorId = :creatorId  AND d.iGroupId = :groupId ) dailyCount, " +
                "COUNT(r.id) AS resourceCount FROM t_resource r WHERE r.iUploaderId = :creatorId AND r.tiRole = :role AND r.iGroupId = :groupId AND r.tiGroupType = :groupType) temp");

        log.debug("专业群组活跃教师统计");
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource()
                .addValue("role", 0)
                .addValue("creatorId", creatorId)
                .addValue("groupId", groupId)
                .addValue("groupType", GroupType.SPEC_GROUP)
                , Integer.class);

    }

    /**
     * 获取活跃教师
     *
     * @param teacherIds 专业ID集合
     * @return List<Teacher>
     * @author lyj
     * @since 2015-5-6
     */
    public List<Teacher> getTeachersByIds(final Set<Long> teacherIds) {
        StringBuilder sql = new StringBuilder("SELECT id,sName,sPicture FROM t_teacher WHERE id IN (:teacherIds)");

        log.debug("获取活跃教师");
        return namedParameterJdbcTemplate.query(sql.toString(), new MapSqlParameterSource().addValue("teacherIds", teacherIds), new RowMapper<Teacher>() {
            @Override
            public Teacher mapRow(ResultSet resultSet, int i) throws SQLException {
                Teacher teacher = new Teacher();

                teacher.setId(resultSet.getLong("id"));
                teacher.setName(resultSet.getString("sName"));
                teacher.setPicture(resultSet.getString("sPicture"));

                return teacher;
            }
        });
    }
}
